﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Comm.Tables
{
    internal static class TableDAL
    {
        private static TableColumnEntity GetColumnByReader(IDataReader dr)
        {
            TableColumnEntity column = new TableColumnEntity();
            column.ColumnID = dr.GetInt32(0);
            column.TableID = dr.GetInt32(1);
            column.Name = dr.IsDBNull(2) ? null : dr.GetString(2);
            column.Label = dr.IsDBNull(3) ? null : dr.GetString(3);
            column.IsEnable = dr.GetBoolean(4);

            return column;
        }

        internal static TableColumnEntity[] GetTableColumnList(int tableID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<TableColumnEntity> columnlst = new List<TableColumnEntity>();

            string SelectColumn = @"
                SELECT [ColumnID],[TableID],[Name],[Label],[IsEnable]
                FROM [TableColumn] WHERE TableID=@TableID 
                ORDER BY Sort
	           ";

            DbCommand SelectMemberCommand = db.GetSqlStringCommand(SelectColumn);
            db.AddInParameter(SelectMemberCommand, "@TableID", DbType.Int32, tableID);

            using (IDataReader dr = db.ExecuteReader(SelectMemberCommand))
            {
                while (dr.Read())
                {
                    TableColumnEntity column = GetColumnByReader(dr);
                    columnlst.Add(column);
                }
            }

            return columnlst.ToArray();
        }

        internal static int SetTableColumnSelect(int tableID, string columnName)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateColumn = @"
            UPDATE [TableColumn]
                   SET [IsEnable] = ([IsEnable]+1)%2
                 WHERE [Name] = @Name AND TableID=@TableID";

            DbCommand UpdateColumnCommand = db.GetSqlStringCommand(UpdateColumn);
            db.AddInParameter(UpdateColumnCommand, "@TableID", DbType.Int32, tableID);
            db.AddInParameter(UpdateColumnCommand, "@Name", DbType.String, columnName);
            result = db.ExecuteNonQuery(UpdateColumnCommand);

            return result;
        }

        internal static TableSortEntity GetTableSort(int tableID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            TableSortEntity sort = new TableSortEntity();

            string SelectColumn = @"
                SELECT [SortID],[TableID],[Name],[IsAsc]
                FROM [TableSort] WHERE TableID=@TableID
	           ";

            DbCommand SelectColumnCommand = db.GetSqlStringCommand(SelectColumn);
            db.AddInParameter(SelectColumnCommand, "@TableID", DbType.Int32, tableID);

            using (IDataReader dr = db.ExecuteReader(SelectColumnCommand))
            {
                while (dr.Read())
                {
                    sort.SortID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
                    sort.TableID = dr.IsDBNull(1) ? 0 : dr.GetInt32(1);
                    sort.Name = dr.IsDBNull(2) ? null : dr.GetString(2);
                    sort.IsAsc = dr.IsDBNull(3) ? false : dr.GetBoolean(3);
                }
            }

            return sort;
        }

        internal static int UpdateTableSort(TableSortEntity sort)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateTableSort = @"
            UPDATE [TableSort]
            SET [Name] = @Name, [IsAsc] = @IsAsc
            WHERE [TableID] = @TableID";

            DbCommand UpdateTableSortCommand = db.GetSqlStringCommand(UpdateTableSort);
            db.AddInParameter(UpdateTableSortCommand, "@Name", DbType.String, sort.Name);
            db.AddInParameter(UpdateTableSortCommand, "@IsAsc", DbType.Boolean, sort.IsAsc);
            db.AddInParameter(UpdateTableSortCommand, "@TableID", DbType.Int32, sort.TableID);
            result = db.ExecuteNonQuery(UpdateTableSortCommand);

            return result;
        }
    }
}
